package org.lanqiao.tjut.model;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.lanqiao.tjut.bean.TBStudentBean;
import org.lanqiao.tjut.db.DBFactory;
import org.lanqiao.tjut.myUtils.MyUtils;

public class StudentInfoModel {
	
	public int deleteStudentInfoById(String stuId) {
		int re_i=0;
		//删除操作sql
		/*
		 * 逻辑删除：使用update修改标志位字段即可
		 * 物理删除：使用delete语句进行删除操作即可
		 * 都需要条件where，否则删除整张表
		 */
		String sbSql=" update student set isdeleted=1 where stu_id=?";
		//查询SQL参数集
		List<Object> lstParas=new ArrayList<>();
		lstParas.add(stuId);
		
		re_i=DBFactory.getDBDriverInstance().update(sbSql.toString(), lstParas.toArray());
		return re_i;
	}
	
	
	public List<TBStudentBean> queryStudentInfoByParas(TBStudentBean stuB){
		List<TBStudentBean> lstStus=null;
		StringBuilder sbSql=new StringBuilder("select * from student where isdeleted=0");
		List<Object> lstParas = new ArrayList<>();
		if(stuB!=null){
			String stuName=stuB.getStudent_name();
			if(stuName != null && !stuName.equals("")){
				sbSql.append(" and student_name like ?");
				lstParas.add("%"+stuName+"%");
				
			}
			String stuSex=stuB.getSex();
			if(stuSex != null && !stuSex.equals("")){
				sbSql.append(" and sex = ?");
				lstParas.add(stuSex);
				
			}
			String stuadress=stuB.getAddress_id();
			if(stuadress !=null&&!stuadress.equals("")){
				sbSql.append(" and address_id = ?");
				lstParas.add(stuadress);
			}
			String stuTel=stuB.getStu_tel();
			if(stuTel !=null&&!stuTel.equals("")){
				sbSql.append(" and stu_tel = ?");
				lstParas.add(stuTel);
			}
			Date stubirthday=stuB.getBirthday();
			if(stubirthday!=null){
				sbSql.append(" and birthday >= to_date(?,'yyyy-mm-dd hh24:mi:ss')");
				lstParas.add(MyUtils.convertDate2String(stubirthday, "yyyy-MM-dd HH:mm:ss"));
				
			}
			Date stubirthday2=stuB.getBirthday2();
			if(stubirthday2!=null){
				sbSql.append(" and birthday <= to_date(?,'yyyy-mm-dd hh24:mi:ss')");
				lstParas.add(MyUtils.convertDate2String(stubirthday2, "yyyy-MM-dd HH:mm:ss"));
				
			}
		}
		sbSql.append(" order by stu_id desc");
		lstStus=DBFactory.getDBDriverInstance().query(sbSql.toString(),
				new BeanListHandler<>(TBStudentBean.class), lstParas.toArray());
		return lstStus;
	}
	
	
	public int saveOrUpdateStudentInfo(TBStudentBean stuB){
		int re_i=0;
        
		StringBuilder sbSql=new StringBuilder();
		
		List<Object> lstParas=new ArrayList<>();
		if(stuB !=null && stuB.getStu_id() == null){
			//新增操作
			sbSql.append(" insert into student(student_name,sex,birthday,address_id,stu_tel,stu_img)"+"values(?,?,to_date(?,'yyyy-mm-dd hh24-mi-ss'),?,?,?)");
			
			lstParas.add(stuB.getStudent_name());
			lstParas.add(stuB.getSex());
			lstParas.add(MyUtils.convertDate2String(stuB.getBirthday(),"yyyy-MM-dd HH:mm:ss"));
			lstParas.add(stuB.getAddress_id());
			lstParas.add(stuB.getStu_tel());
			lstParas.add(stuB.getStu_img());
		}else{
			//修改操作
			sbSql.append(" update student set student_name=?,sex=?,birthday=to_date(?,'yyyy-mm-dd hh24-mi-ss'),address_id=?,stu_tel=?");

			lstParas.add(stuB.getStudent_name());
			lstParas.add(stuB.getSex());
			lstParas.add(MyUtils.convertDate2String(stuB.getBirthday(),"yyyy-MM-dd HH:mm:ss"));
			lstParas.add(stuB.getAddress_id());
			lstParas.add(stuB.getStu_tel());
			
			if(stuB.getStu_img()!=null&&!stuB.getStu_img().equals("")){
				sbSql.append(" ,stu_img=? ");
				lstParas.add(stuB.getStu_img());
			}
			sbSql.append(" where stu_id=? ");
			lstParas.add(stuB.getStu_id());
		}
		
		
		re_i = DBFactory.getDBDriverInstance().update(sbSql.toString(), lstParas.toArray());
		return re_i;
	}


	public TBStudentBean getStudentInfoById(String stuId) {
		TBStudentBean stuB=null;
		//查询sql
		String sbSql="select * from student where isdeleted=0 and stu_id=?";
		//查询SQL参数集
		List<Object> lstParas=new ArrayList<>();
		lstParas.add(stuId);
		
		stuB=DBFactory.getDBDriverInstance().query(sbSql.toString(),new BeanHandler<>(TBStudentBean.class), lstParas.toArray());
		return stuB;
	}

}
